﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace HandlingProcedures.DataBase
{
    /// <summary>
    /// Реализация хранилища данных на основе базы данных MySql
    /// </summary>
    class OperationFailedInDataBaseException : Exception
    {
        public OperationFailedInDataBaseException() : base() { }
        public OperationFailedInDataBaseException(string message) : base(message) { }
    }

    class DataBaseMySql : IRepository
    {
        static string connectionString = "server=localhost;database=sanatorium;uid=root;password=";

        #region queries
        string queryGetClients = @"SELECT * 
                                    FROM Clients
                                    order by Id_Client;";

        string queryGetProcedures = @"SELECT *
                                        FROM Procedures
                                        order by Id_Procedure";

        string queryGetScheduleForClient = @"SELECT s.Id_schedule, p.ProcName, cs.ProcDate, s.TimeStart, s.TimeEnd, r.ProcRoomNumber
                                        FROM ClientsSchedule AS cs, Schedule AS s, Procedures AS p, ProcRoom AS r
                                        WHERE cs.Id_Client = @id 
                                            AND cs.Id_Schedule = s.Id_Schedule 
                                            AND s.Id_Procedure = p.Id_Procedure
                                            AND s.Id_ProcRoom = r.Id_ProcRoom
                                        order by cs.ProcDate";

        string queryGetInfoAboutClient = @"SELECT CheckIn, CheckOut
                                            FROM RoomsClients
                                                WHERE Id_Client = @id";

        string queryGetFreeRooms = @"SELECT s.Id_schedule, r.ProcRoomNumber, s.TimeStart, s.TimeEnd
                                        FROM Schedule AS s, ProcRoom AS r
                                            WHERE s.Id_Procedure = (SELECT Id_Procedure
                                                                        FROM Procedures
                                                                            WHERE ProcName = @procName)
                                                AND s.Id_DayOfWeek = @dayOfWeek
                                                AND s.CountProcInRoom >(SELECT Count(*)
                                                                        FROM ClientsSchedule AS iner
                                                                            WHERE ProcDate = @date AND iner.Id_Schedule = s.Id_Schedule)
												AND s.Id_ProcRoom = r.Id_ProcRoom";

        string queryGetProcRooms = @"SELECT DISTINCT pr.ProcRoomNumber
                                        FROM ProcRoom AS pr, procedures AS p, schedule AS s
                                            WHERE pr.Id_ProcRoom = s.Id_ProcRoom 
                                                AND s.Id_DayOfWeek = @dayOfWeek 
                                                AND s.Id_Procedure = p.Id_Procedure
                                                AND p.ProcName = @procName;";

        string queryGetAssignedProcRooms = @"SELECT count(s.Id_Schedule) AS count, s.Id_Schedule, s.TimeStart, s.TimeEnd, pr.ProcRoomNumber
                                                FROM ProcRoom AS pr, procedures AS p, schedule AS s, ClientsSchedule AS cs
                                                    WHERE s.Id_Procedure = p.Id_Procedure AND
	                                                    s.Id_schedule = cs.Id_Schedule AND
	                                                    s.Id_ProcRoom = pr.Id_ProcRoom AND
	                                                    cs.ProcDate = @date AND
	                                                    p.ProcName = @procName AND
	                                                    pr.ProcRoomNumber LIKE @room
                                                        group by s.Id_Schedule;";

        string queryDeleteProcedureForClient = @"DELETE FROM clientsschedule
                                                WHERE Id_Client =  @id_client AND
                                                    Id_Schedule = @id_schedule AND 
                                                    ProcDate = @date;";

        string queryInsertEntryToSchedule = @"INSERT INTO clientsschedule (ProcDate, Id_Client, Id_Schedule)
                                                VALUES (@date,  @id_client, @id_schedule);";

        #endregion

        MySqlConnection connection;

        public DataBaseMySql()
        {
            connection = new MySqlConnection(connectionString);
        }

        private MySqlConnection GetOpenConnection()
        {
            if (connection.State != System.Data.ConnectionState.Open)
            {
                    connection.Open();
            }
            return connection;
        }

        private MySqlDataReader ExecuteQuery(string query)
        {
            //установка соединнения
            if (connection.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    connection.Open();
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
            }

            //подготовка к выполнению запроса
            MySqlCommand command = new MySqlCommand(query, connection);
            MySqlDataReader reader;

            //попытка выполнить запрос
            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Ошибка базы данных:" + Environment.NewLine + ex.Message);
            }
            finally
            {
                //connection.Close();
            }

            return reader;
        }


        //Загрузка комнат в которых проводятся процедуры
        public List<string> LoadProcRooms(string procedureName, DateTime dateOfProcedure)
        {
            int day = (int)dateOfProcedure.DayOfWeek;
            List<string> procRooms = new List<string>();

            MySqlCommand command = new MySqlCommand(queryGetProcRooms, GetOpenConnection());
            command.Parameters.Add("@procName", MySqlDbType.String);
            command.Parameters["@procName"].Value = procedureName;
            command.Parameters.Add("@dayOfWeek", MySqlDbType.Int32);
            command.Parameters["@dayOfWeek"].Value = day == 0 ? 7 : day;

            MySqlDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    procRooms.Add(reader.GetString("ProcRoomNumber"));
                }
            }
            finally
            {
                reader.Close();
            }

            return procRooms;
        }

        //Загрузка назначенных процедур
        public List<MedicalProcedureInRoom> LoadAssignedRoomsForProcedure(string procedureName, DateTime dateOfProcedure, string room)
        {
            MySqlCommand command = new MySqlCommand(queryGetAssignedProcRooms, GetOpenConnection());
            command.Parameters.Add("@procName", MySqlDbType.String);
            command.Parameters["@procName"].Value = procedureName;
            command.Parameters.Add("@date", MySqlDbType.Date);
            command.Parameters["@date"].Value = dateOfProcedure;
            command.Parameters.Add("@room", MySqlDbType.String);
            command.Parameters["@room"].Value = (room == null ? "%" : room);
            MySqlDataReader reader = command.ExecuteReader();

            List<MedicalProcedureInRoom> medicalRoom = new List<MedicalProcedureInRoom>();
            try
            {
                while (reader.Read())
                {
                    medicalRoom.Add(new MedicalProcedureInRoom(new MedicalProcedure(reader.GetInt32("Id_schedule"), reader.GetString("ProcRoomNumber"), DateTime.Parse(reader.GetString("TimeStart")).TimeOfDay, DateTime.Parse(reader.GetString("TimeEnd")).TimeOfDay, true), reader.GetInt32("count")));
                }
            }
            finally
            {
                reader.Close();
            }

            return medicalRoom;
        }

        public List<Client> LoadClients()
        {
            List<Client> result = new List<Client>();
            MySqlDataReader reader = ExecuteQuery(queryGetClients);

            try
            {
                while (reader.Read())
                {
                    result.Add(new Client(reader.GetInt32("Id_Client"), reader.GetString("Name"), reader.GetString("Surname"), reader.GetString("Patronymic")));
                }
            }
            finally
            {
                reader.Close();
                connection.Close();
            }           
            
            return result;
        }

        public List<string> LoadProcedures()
        {
            List<string> procedures = new List<string>();
            MySqlDataReader reader = ExecuteQuery(queryGetProcedures);
            try
            {
                while (reader.Read())
                {
                    procedures.Add(reader.GetString("ProcName"));
                }
            }
            finally
            {
                reader.Close();
                connection.Close();
            }
            reader.Close();
            return procedures;
        }

        public List<MedicalProcedure> LoadSchedule(int id)
        {
            List<MedicalProcedure> procedures = new List<MedicalProcedure>();
            MySqlCommand command = new MySqlCommand(queryGetScheduleForClient, GetOpenConnection());
            command.Parameters.Add("@id", MySqlDbType.Int32);
            command.Parameters["@id"].Value = id;

            MySqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    procedures.Add(new MedicalProcedure(reader.GetInt32("Id_Schedule"), reader.GetString("ProcName"), reader.GetString("ProcRoomNumber"), reader.GetTimeSpan("TimeStart"), reader.GetTimeSpan("TimeEnd"), Convert.ToDateTime(reader.GetString("ProcDate")), true
    ));
                }
            }
            finally
            {
                reader.Close();
                connection.Close();
            }
            return procedures;
        }

        public ClientInformation GetInformationAboutClient(int id)
        {
            ClientInformation info = null;

            MySqlCommand command = new MySqlCommand(queryGetInfoAboutClient, GetOpenConnection());
            command.Parameters.Add("@id", MySqlDbType.Int32);
            command.Parameters["@id"].Value = id;
            MySqlDataReader reader = command.ExecuteReader();

            try
            {
                if (reader.Read())
                {
                    info = new ClientInformation(Convert.ToDateTime(reader.GetString("CheckIn")), Convert.ToDateTime(reader.GetString("CheckOut")));
                }
            }
            finally
            {
                reader.Close();
                connection.Close();
            }
            return info;
        }

        public List<MedicalProcedure> LoadFreeRoomsForProcedure(string procedureName, DateTime dateOfProcedure)
        {
            int day = (int)dateOfProcedure.DayOfWeek;
            List<MedicalProcedure> freeRooms = null;

            MySqlCommand command = new MySqlCommand(queryGetFreeRooms, GetOpenConnection());
            command.Parameters.Add("@procName", MySqlDbType.String);
            command.Parameters["@procName"].Value = procedureName;
            command.Parameters.Add("@dayOfWeek", MySqlDbType.Int32);
            command.Parameters["@dayOfWeek"].Value = day == 0 ? 7 : day;
            command.Parameters.Add("@date", MySqlDbType.Date);
            command.Parameters["@date"].Value = dateOfProcedure;
            MySqlDataReader reader = command.ExecuteReader();

            freeRooms = new List<MedicalProcedure>();
            try
            {
                while (reader.Read())
                {
                    freeRooms.Add(new MedicalProcedure(reader.GetInt32("Id_schedule"), reader.GetString("ProcRoomNumber"), DateTime.Parse(reader.GetString("TimeStart")).TimeOfDay, DateTime.Parse(reader.GetString("TimeEnd")).TimeOfDay, false));
                }
            }
            finally
            {
                reader.Close();
            }

            return freeRooms;
        }

        public void DeleteProcedure(int clientId, IEnumerable<MedicalProcedure> procedures)
        {
            MySqlCommand command = new MySqlCommand(queryDeleteProcedureForClient, GetOpenConnection());

            command.Parameters.Add("@id_client", MySqlDbType.Int32);
            command.Parameters.Add("@id_schedule", MySqlDbType.Int32);
            command.Parameters.Add("@date", MySqlDbType.Date);            
            command.Parameters["@id_client"].Value = clientId;
            
            try
            {
                foreach (MedicalProcedure procedure in procedures)
                {
                    command.Parameters["@id_schedule"].Value = procedure.Shedule_id;
                    command.Parameters["@date"].Value = procedure.Date;
                    if (command.ExecuteNonQuery() == 0)
                    {
                        throw new OperationFailedInDataBaseException("Удаление процедуры не было выполнено");
                    }
                    procedure.InRepository = false;
                }
            }
            finally
            {
                connection.Close();
            }
        }

        public void SaveNewProceduresForClient(int clientId, IEnumerable<MedicalProcedure> procedures)
        {
            MySqlCommand command = new MySqlCommand(queryInsertEntryToSchedule, GetOpenConnection());

            command.Parameters.Add("@id_client", MySqlDbType.Int32);
            command.Parameters.Add("@date", MySqlDbType.Date);
            command.Parameters.Add("@id_schedule", MySqlDbType.Int32);

            command.Parameters["@id_client"].Value = clientId;

            try
            {
                foreach (MedicalProcedure procedure in procedures)
                {
                    command.Parameters["@id_schedule"].Value = procedure.Shedule_id;
                    command.Parameters["@date"].Value = procedure.Date;
                    if (command.ExecuteNonQuery() == 0)
                    {
                        throw new OperationFailedInDataBaseException("Сохранение процедуры не было выполнено");
                    }
                    procedure.InRepository = true;
                }
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
